Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# CUMSUM

The CUMSUM function computes cumulative totals over time or over another dimension. When the data being totaled is one-dimensional, CUMSUM produces a single series of totals, one for all values of the dimension. When the data has dimensions other than the one being totaled over, CUMSUM produces a separate series of totals for each combination of values in the status of the other dimensions.

Return Value

DECIMAL

Syntax

CUMSUM(cum-expression [STATUS] total-dim [reset-dim] [INSTAT])

Arguments

cum-expression

  A numeric variable or calculation whose values you want to total, for example UNITS.

STATUS

  When cum-expression is multidimensional, CUMSUM creates a temporary variable to use while processing the function. When you specify the STATUS keyword, CUMSUM uses the current status instead of the default status of the dimensions for calculating the size of this temporary variable. When the dimensions of the expression are limited to a few values and are physically fragmented, you can improve the performance of CUMSUM by specifying STATUS.

  When you use CUMSUM with the STATUS keyword in an expression that requires going outside of status for results (for example, with the LEAD or LAG functions or with a qualified data reference), the results outside of status will be returned as `NA`.

  Note: When you specify the STATUS keyword when the data being totaled is one-dimensional, an error results

total-dim

  The dimension of cum-expression over which you want to total.

reset-dim

  Specifies that the cumulative totals in a series should start over with each new reset dimension value, for example at the start of each new year. The reset dimension can be any of the following:

  - Any dimension related to total-dim through an explicitly defined relation.

  - Any dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR, when total-dim also has a type of DAY, WEEK, MONTH, QUARTER, or YEAR. CUMSUM uses the implicit relation between the two dimensions, so they do not need to be related through an explicit relation. See "Overriding an Implicit Relation".

  - A relation dimensioned by total-dim. CUMSUM uses the related dimension as the reset dimension. This enables you to choose which relation is used when there is more than one.

INSTAT

  Specifies that CUMSUM uses only the values of total-dim that are currently in status. When you do not specify INSTAT, CUMSUM produces a total for all the values of total-dim, independent of its current status. See "INSTAT Ignores Current Status By Default".

Notes

Overriding an Implicit Relation

When you specify dimensions with a type of DAY, WEEK, MONTH, QUARTER, or YEAR for both the total-dim argument and the reset-dim argument, CUMSUM uses the implicit relation between the two dimensions even when an explicit relation exists. However, you can override the default and use the explicit relation by specifying the name of the relation for the reset-dim argument.

INSTAT Ignores Current Status By Default

Unless you specify the INSTAT keyword, CUMSUM ignores the current status in calculating totals. Suppose MONTH is the dimension being totaled over (and INSTAT has not been specified). The CUMSUM total for a given month uses the values for all preceding months, even when some are not in the status. When a reset dimension is specified, the total for a given month uses the values for all preceding months that correspond to the same value of the reset dimension (for example, all preceding months in the same year). To calculate year-to-date totals, specify YEAR as the reset dimension.

Examples

Example 9-31 Multiple CUMSUM Calculations

This example shows cumulative `units` totals for tents and canoes in the Atlanta district for the first six months of 1996. The report shows the units figures themselves, year-to-date totals calculated using `year` as the reset dimension, and totals calculated with no reset dimension using all preceding months. Assume that you issue the following statements.

    LIMIT district TO 'Atlanta'
    LIMIT product TO 'Tents' 'Canoes'
    LIMIT month TO 'Jan96' TO 'Jun96'
    REPORT DOWN month units CUMSUM(units, month year) -
       CUMSUM(units, month)

The following report is displayed.

    DISTRICT: ATLANTA
             ------------------------PRODUCT------------------------
             ---------TENTS------------- ---------CANOES------------
                   CUMSUM(UNI                   CUMSUM(UNI
                    TS, MONTH CUMSUM(UNI         TS, MONTH CUMSUM(UNI
    MONTH    UNITS    YEAR)   TS, MONTH)  UNITS    YEAR)   TS, MONTH)
    -----  -------- --------- ---------- ------- --------- ----------
    Jan96      279       279      5,999      281       281      5,162
    Feb96      305       584      6,304      309       590      5,471
    Mar96      356       940      6,660      386       976      5,857
    Apr96      537     1,477      7,197      546     1,522      6,403
    May96      646     2,123      7,843      525     2,047      6,928
    Jun96      760     2,883      8,603      608     2,655      7,536

The totals for CUMSUM(UNITS, MONTH) include values for all months beginning with the first month, JAN95. The totals for CUMSUM(UNITS, MONTH YEAR) include only the values starting with JAN96.

Example 9-32 Resetting for a Quarter

This example shows cumulative totals for the same products and district, for the entire year 1996. Because `quarter` is specified as the reset dimension, totals start accumulating at the beginning of each quarter. The cumulative totals for `Jan96`, `Apr96`, `Jul96`, and `Oct96` are the same as the units figures for those months. Assume that you issue the following statements.

    LIMIT district TO 'Atlanta'
    LIMIT product TO 'Tents' 'Canoes'
    limit month TO year 'Yr96'
    REPORT DOWN month units CUMSUM(units, month quarter)

A report displays.

    DISTRICT: ATLANTA
                 ------------------PRODUCT------------------
                 --------TENTS-------- -------CANOES--------
                              CUMSUM(UNI          CUMSUM(UNI
                               TS, MONTH           TS, MONTH
    MONTH           UNITS     QUARTER)    UNITS     QUARTER)
    ------------ ---------- ---------- ---------- ----------
    Jan96             279        279        281        281
    Feb96             305        584        309        590
    Mar96             356        940        386        976
    Apr96             537        537        546        546
    May96             646      1,183        525      1,071
    Jun96             760      1,943        608      1,679
    Jul96             852        852        626        626
    Aug96             730      1,582        528      1,154
    Sep96             620      2,202        520      1,674
    Oct96             554        554        339        339
    Nov96             380        934        309        648
    Dec96             284      1,218        288        936